﻿DECLARE @Var_SecondDeptCode VARCHAR(10)
SET @Var_SecondDeptCode = (@SecondDeptCode_In)

SELECT  RTRIM(a.KSCode) AS KSCode
       ,CASE a.KSName WHEN '内一科' THEN '神经内科、（囊虫病科）' 
                      WHEN '内二科' THEN '呼吸、消化内科'
                      WHEN '内三科' THEN '心血管、内分泌内科'
                      WHEN '内四科' THEN '感染性疾病科'
                      WHEN '内五科' THEN '针灸、推拿、睡眠医学科'
                      WHEN '精神科' THEN '精神康复科'
                      WHEN '外一科' THEN '普外、肿瘤科'
                      WHEN '外二科' THEN '泌尿外科'
                      WHEN '外三科' THEN '骨科'
                      WHEN '眼科' THEN '眼耳鼻喉科'
                      WHEN '中痔科' THEN '肛肠科'
                      WHEN '急诊' THEN '急诊科；重症医学科'
        ELSE a.KSName              
        END AS KSName
       ,RTRIM(b.LBName) AS Description
FROM dbo.MZSchedule a(NOLOCK) JOIN dbo.KSCODE b(NOLOCK) ON a.KSCode = b.CODE
WHERE 
Weekday = DATEPART(WEEKDAY,GETDATE())
AND KSCode LIKE + '%' + @Var_SecondDeptCode
AND (STATUS IS NULL OR STATUS = 2)
GROUP BY RTRIM(a.KSCode),KSName,b.LBNAME